﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class MoneyDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoMoney model)
        {
            model.MON_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Money]");
            sql.Append("([MON_ID],[MON_CONTESTS],[MON_ITEM],[MON_COMMENT],[MON_NUMBER],[MON_DEALER],[MON_DATE],[MON_TYPE])");
            sql.Append("VALUES");
            sql.Append("(@MON_ID,@MON_CONTESTS,@MON_ITEM,@MON_COMMENT,@MON_NUMBER,@MON_DEALER,@MON_DATE,@MON_TYPE)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoMoney model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Money] SET ");
            sql.Append("MON_CONTESTS=@MON_CONTESTS");
            sql.Append(",MON_ITEM=@MON_ITEM");
            sql.Append(",MON_COMMENT=@MON_COMMENT");
            sql.Append(",MON_NUMBER=@MON_NUMBER");
            sql.Append(",MON_DEALER=@MON_DEALER");
            sql.Append(",MON_DATE=@MON_DATE");
            sql.Append(",MON_TYPE=@MON_TYPE");
            sql.Append(" WHERE ");
            sql.Append("MON_ID=@MON_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string mON_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM Money");
            sql.Append(" WHERE ");
            sql.Append("MON_ID=@MON_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@MON_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = mON_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoMoney GetModel(string mON_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT MON_ID,MON_CONTESTS,MON_ITEM,MON_COMMENT,MON_NUMBER,MON_DEALER,MON_DATE,MON_TYPE FROM Money");
            sql.Append(" WHERE ");
            sql.Append("MON_ID=@MON_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@MON_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = mON_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoMoney> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoMoney> GetContestMoney(string contestId, int moneyType)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT MON_ID,MON_CONTESTS,MON_ITEM,MON_COMMENT,MON_NUMBER,MON_DEALER,MON_DATE,MON_TYPE FROM Money");
            sql.Append(" WHERE ");
            sql.Append(" MON_CONTESTS=@MON_CONTESTS AND MON_TYPE=@MON_TYPE");
            SqlParameter[] parameter = new SqlParameter[2];
            parameter[0] = new SqlParameter("@MON_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            parameter[1] = new SqlParameter("@MON_TYPE", SqlDbType.Int);
            parameter[1].Value = moneyType;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoMoney> list = GetList(table);
            return list;
        }

        private IList<BoMoney> GetList(DataTable table)
        {
            List<BoMoney> list = new List<BoMoney>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoMoney model = new BoMoney();
                    model.MON_ID = (string)table.Rows[i]["MON_ID"];
                    model.MON_CONTESTS = (string)table.Rows[i]["MON_CONTESTS"];
                    model.MON_ITEM = (string)table.Rows[i]["MON_ITEM"];
                    model.MON_COMMENT = (string)table.Rows[i]["MON_COMMENT"];
                    model.MON_NUMBER = (double)table.Rows[i]["MON_NUMBER"];
                    model.MON_DEALER = table.Rows[i]["MON_DEALER"]==System.DBNull.Value?"":(string)table.Rows[i]["MON_DEALER"];
                    model.MON_DATE = table.Rows[i]["MON_DATE"] == System.DBNull.Value ? DateTime.Now : (DateTime)table.Rows[i]["MON_DATE"];
                    model.MON_TYPE = (Int32)table.Rows[i]["MON_TYPE"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoMoney model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@MON_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@MON_CONTESTS",SqlDbType.NVarChar,50),
                new SqlParameter("@MON_ITEM",SqlDbType.NVarChar,50),
                new SqlParameter("@MON_COMMENT",SqlDbType.NVarChar,1024),
                new SqlParameter("@MON_NUMBER",SqlDbType.Float,8),
                new SqlParameter("@MON_DEALER",SqlDbType.NVarChar,50),
                new SqlParameter("@MON_DATE",SqlDbType.DateTime,8),
                new SqlParameter("@MON_TYPE",SqlDbType.Int,4),
            };
            parameters[0].Value = model.MON_ID;
            parameters[1].Value = model.MON_CONTESTS;
            parameters[2].Value = model.MON_ITEM;
            parameters[3].Value = model.MON_COMMENT;
            parameters[4].Value = model.MON_NUMBER;
            parameters[5].Value = model.MON_DEALER;
            parameters[6].Value = model.MON_DATE;
            parameters[7].Value = model.MON_TYPE;
            return parameters;
        }
    }
}